package movieGandalf.database;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import movieGandalf.dataObjects.*;
import movieGandalf.logicGuiConnection.*;
import movieGandalf.search.SearchParam;

public class MovieTools {
	
	//Constants
	
	public static final int MAX_MOVIE_LENGTH = 100;
	public static final int MAX_ACTOR_LENGTH = 100;
	public static final int MAX_DIRECTOR_LENGTH = 100;
	public static final int MAX_COUNTRY_LENGTH = 50;
	public static final int MAX_LANGUAGE_LENGTH = 30;
	public static final int MAX_GENRE_LENGTH = 15;
	public static final int MAX_MPAA_RATING_LENGTH = 5;
	public static final int MAX_DESCRIPTION_LENGTH = -1;
	
	//Connection handlers
	
	private static Connection conn;
	
	private static boolean state;
	
	public static boolean remoteInit(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			MovieTools.conn = DriverManager.getConnection("jdbc:mysql://localhost:3305/DbMysql06","DbMysql06","DbMysql06");
			MovieTools.conn.setAutoCommit(false);
		}
		catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
		MovieTools.state=true;
		return true;
	}
	
	public static boolean init(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			MovieTools.conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DbMysql06","root","Puaehi86");
			MovieTools.conn.setAutoCommit(false);
		}
		catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
		MovieTools.state=true;
		return true;
	}
	
	public static boolean close(){
		MovieTools.state=false;
		
		try {
			MovieTools.conn.close();
		}
		catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
		return true;
	}
	
	public static boolean isInited(){
		return state;
	}
	
	public static Connection getConn(){
		if (!MovieTools.isInited())
			return null;
		
		try{
			if (MovieTools.conn.isValid(5))
				return MovieTools.conn;
		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
		
		return null;
	}
	
	// String validators
	
	public static boolean validator(String name, int maxLength){
		if (name == null)
			return false;
		if (maxLength!=-1 && name.length()>maxLength)
			return false;
		if (name.equals(""))
			return false;
		if (name.contains("�"))
			return false;
		return true;
	}
	
	public static boolean movieValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_MOVIE_LENGTH);
	}
	
	public static boolean actorValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_ACTOR_LENGTH);
	}
	
	public static boolean directorValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_DIRECTOR_LENGTH);
	}
	
	public static boolean countryValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_COUNTRY_LENGTH);
	}
	
	public static boolean languageValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_LANGUAGE_LENGTH);
	}
	
	public static boolean genreValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_GENRE_LENGTH);
	}
	
	public static boolean mpaaRatingValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_MPAA_RATING_LENGTH);
	}
	
	public static boolean descriptionValidator(String name){
		return MovieTools.validator(name, MovieTools.MAX_DESCRIPTION_LENGTH);
	}
	
	// Object parsers
	
	public static Genre parseGenre(final ResultSet rs){
		Object o = null;
		Integer genreID = null;
		String genre = null;
		try{
			o = rs.getObject("genreID");
			if (!(o instanceof Integer))
				return null;
			genreID = (Integer) o;
			
			o = rs.getObject("genre");
			if (o instanceof String)
				genre = (String) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (genre == null || genreID == null)
			return null;
		
		return new GenreImp(genreID,genre);
	}
	
	public static MPAARating parseMPAARating(final ResultSet rs){
		Object o = null;
		Integer mpaaRatingID = null;
		String mpaaRating = null;
		try{
			o = rs.getObject("mpaaRatingID");
			if (!(o instanceof Integer))
				return null;
			mpaaRatingID = (Integer) o;
			
			o = rs.getObject("mpaaRating");
			if (o instanceof String)
				mpaaRating = (String) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (mpaaRating == null || mpaaRatingID == null)
			return null;
		
		return new MPAARatingImp(mpaaRatingID,mpaaRating);
	}
	
	public static Country parseCountry(final ResultSet rs){
		Object o = null;
		Integer countryID = null;
		String country = null;
		try{
			o = rs.getObject("countryID");
			if (!(o instanceof Integer))
				return null;
			countryID = (Integer) o;
			
			o = rs.getObject("country");
			if (o instanceof String)
				country = (String) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (country == null || countryID == null)
			return null;
		
		return new CountryImp(countryID,country);
	}
	
	public static Language parseLanguage(final ResultSet rs){
		Object o = null;
		Integer languageID = null;
		String language = null;
		try{
			o = rs.getObject("languageID");
			if (!(o instanceof Integer))
				return null;
			languageID = (Integer) o;
			
			o = rs.getObject("language");
			if (o instanceof String)
				language = (String) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (language == null || languageID == null)
			return null;
		
		return new LanguageImp(languageID,language);
	}
	
	public static Role parseRole(final ResultSet rs){
		Object o = null;
		BigInteger actorID = null;
		BigInteger movieID = null;
		String role = null;
		try{
			o = rs.getObject("movieID");
			if (!(o instanceof BigInteger))
				return null;
			movieID = (BigInteger) o;
			
			o = rs.getObject("actorID");
			if (!(o instanceof BigInteger))
				return null;
			actorID = (BigInteger) o;
			
			o = rs.getObject("role");
			if (!(o instanceof String))
				return null;
			role = (String) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (actorID == null || movieID == null)
			return null;
		
		return new RoleImp(movieID,actorID,role);
	}
	
	public static Director parseDirector(final ResultSet rs){
		Object o = null;
		BigInteger actorID = null;
		String actorName = null;
		String imdbName = null;
		
		try{
			o = rs.getObject("actorID");
			if (!(o instanceof BigInteger))
				return null;
			actorID = (BigInteger) o;
			
			o = rs.getObject("actorName");
			if (!(o instanceof String))
				return null;
			actorName = (String) o;
			
			o = rs.getObject("imdbName");
			if (!(o instanceof String))
				return null;
			imdbName = (String) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (imdbName == null || actorID == null)
			return null;
		
		return new DirectorImp(actorID,actorName,imdbName);
	}
	
	public static User parseUser(final ResultSet rs){
		Object o = null;
		BigInteger userID = null;
		String userame = null;
		String password = null;
		Boolean isAdmin = false;
		
		try{
			o = rs.getObject("userID");
			if (!(o instanceof BigInteger))
				return null;
			userID = (BigInteger) o;
			
			o = rs.getObject("username");
			if (!(o instanceof String))
				return null;
			userame = (String) o;
			
			o = rs.getObject("password");
			if (!(o instanceof String))
				return null;
			password = (String) o;
			
			o = rs.getObject("admin");
			if (o instanceof Boolean)
				isAdmin = (Boolean) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (userID == null || userame == null || password == null)
			return null;
		
		return new UserImp(userID,userame,password,isAdmin);
	}
	
	public static Actor parseActor(final ResultSet rs){
		Object o = null;
		BigInteger actorID = null;
		String actorName = null;
		String imdbName = null;
		Gender gender = null;
		String genderString = null;
		
		try{
			o = rs.getObject("actorID");
			if (!(o instanceof BigInteger))
				return null;
			actorID = (BigInteger) o;
			
			o = rs.getObject("actorName");
			if (!(o instanceof String))
				return null;
			actorName = (String) o;
			
			o = rs.getObject("imdbName");
			if (!(o instanceof String))
				return null;
			imdbName = (String) o;
			
			o = rs.getObject("gender");
			if ((o instanceof String)){
				genderString = (String) o;
				gender = Gender.parseGender(genderString);
			}
		}
		catch (Exception e){
			return null;
		}
		
		if (imdbName == null || actorID == null)
			return null;
		
		return new ActorImp(actorID,actorName,imdbName,gender);
	}
	
	
	public static Movie parseMovie(final ResultSet rs){
		Object o = null;
		BigInteger movieID = null;
		String movieName = null;
		String imdbName = null;
		int year = 0;
		BigInteger directorID = null;
		int length = 0;
		Boolean isColor = null;
		Float userRating = null;
		
		Country country = MovieTools.parseCountry(rs);
		Language language = MovieTools.parseLanguage(rs);
		MPAARating mpaaRating = MovieTools.parseMPAARating(rs);
		
		try{
			o = rs.getObject("movieID");
			if (!(o instanceof BigInteger))
				return null;
			movieID = (BigInteger) o;
			
			o = rs.getObject("movieName");
			if (!(o instanceof String))
				return null;
			movieName = (String) o;
			
			o = rs.getObject("imdbName");
			if (!(o instanceof String))
				return null;
			imdbName = (String) o;
			
			o = rs.getObject("year");
			if (!(o instanceof Integer))
				return null;
			year = (Integer) o;
			
			o = rs.getObject("directorID");
			if ((o instanceof BigInteger))
				directorID = (BigInteger) o;
			
			o = rs.getObject("length");
			if (o instanceof Integer)
				length = (Integer) o;
			
			o = rs.getObject("isColor");
			if (o instanceof Boolean)
				isColor = (Boolean) o;
			
			o = rs.getObject("userRating");
			if ((o instanceof Float))
				userRating = (Float) o;
		}
		catch (Exception e){
			return null;
		}
		
		if (movieID == null || movieName == null || imdbName == null | year == -1)
			return null;
		
		return new MovieImp(movieID, movieName, imdbName, year, length, country, language, directorID, isColor, mpaaRating, userRating);
	}
	
	
	// Database accessors
	
	
	public static List<Movie> getMoviesByFilter(Connection conn, List<SearchParam> searchParams){
		return getMoviesByFilter(conn,searchParams,100,0);
	}
	
	public static List<Movie> getMoviesByFilter(Connection conn, List<SearchParam> searchParams, Integer limit, Integer offset){
		PreparedStatement getMovies = null;
		List<Movie> movies = new LinkedList<Movie>();
		Movie movie = null;
		boolean isFirst = true;
		
		try{
			String sql = "SELECT MoviesWithData.movieID AS movieID, MoviesWithData.imdbName AS imdbName, movieName, year, countryID, languageID, MoviesWithData.directorID AS directorID, length, isColor, mpaaRatingID, userRating, country, language, mpaaRating FROM MoviesWithData";
			String where = "";
			for (SearchParam sp : searchParams){
				if (!sp.getJoin().equals(""))
					sql += " " + sp.getJoin();
				if (sp.isParamOnJoin())
					continue;
				
				if (isFirst){
					isFirst = false;
					where += " WHERE ";
				}
				else{
					where += " AND ";
				}
				where += sp.getParam();
			}
			
			sql += where;
			sql += " LIMIT ? OFFSET ?";
			getMovies = conn.prepareStatement(sql);
			
			int i=1;
			for (SearchParam sp : searchParams){
				if (!sp.isParamOnJoin())
					continue;
				
				getMovies.setObject(i, sp.getValue());
				i++;
			}
			
			for (SearchParam sp : searchParams){
				if (sp.isParamOnJoin())
					continue;
				
				getMovies.setObject(i, sp.getValue());
				i++;
			}
			
			getMovies.setObject(i, limit);
			getMovies.setObject(i+1, offset);
			
			ResultSet rs = getMovies.executeQuery();
			while (rs.next()){
				movie = MovieTools.parseMovie(rs);
				if (movie != null)
					movies.add(movie);
			}
			getMovies.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return movies;
	}
	
	public static List<Integer> getGenreIDs(Connection conn, BigInteger movieID){
		PreparedStatement getGenreIDs = null;
		Object o = null;
		Integer genreID = null;
		List<Integer> genreIDs = new LinkedList<Integer>();
		
		try{
			getGenreIDs = conn.prepareStatement("SELECT genreID FROM MovieGenres WHERE movieID=?");
			getGenreIDs.setObject(1, movieID);
			ResultSet rs = getGenreIDs.executeQuery();
			while (rs.next()){
				o = rs.getObject("genreID");
				if (!(o instanceof Integer))
					continue;
				genreID = (Integer) o;
				genreIDs.add(genreID);
			}
			getGenreIDs.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return genreIDs;
	}
	
	public static List<Genre> getGenres(Connection conn, BigInteger movieID){
		PreparedStatement getGenres = null;
		Object o = null;
		Genre genre = null;
		List<Genre> genres = new LinkedList<Genre>();
		
		try{
			getGenres = conn.prepareStatement("SELECT Genres.genreID AS genreID, genre FROM Genres INNER JOIN MovieGenres ON Genres.genreID=movieGenres.genreID WHERE movieID=?");
			getGenres.setObject(1, movieID);
			ResultSet rs = getGenres.executeQuery();
			while (rs.next()){
				genre = MovieTools.parseGenre(rs);
				genres.add(genre);
			}
			getGenres.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return genres;
	}
	
	public static String getLanguage(Connection conn, Integer languageID){
		PreparedStatement getLanguage = null;
		Object o = null;
		String language = null;
		
		try{
			getLanguage = conn.prepareStatement("SELECT language FROM Languages WHERE languageID=?");
			getLanguage.setObject(1, languageID);
			ResultSet rs = getLanguage.executeQuery();
			if (rs.next()){
				o = rs.getObject("language");
				if (!(o instanceof String))
					return null;
				language = (String) o;
			}
			getLanguage.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return language;
	}
	
	public static Integer getLanguageID(Connection conn, String language){
		PreparedStatement getLanguageID = null;
		Object o = null;
		Integer languageID = null;
		
		try{
			getLanguageID = conn.prepareStatement("SELECT languageID FROM Languages WHERE language=?");
			getLanguageID.setString(1, language);
			ResultSet rs = getLanguageID.executeQuery();
			if (rs.next()){
				o = rs.getObject("languageID");
				if (!(o instanceof Integer))
					return null;
				languageID = (Integer) o;
			}
			getLanguageID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return languageID;
	}
	
	public static String getCountry(Connection conn, Integer countryID){
		PreparedStatement getCountry = null;
		Object o = null;
		String country = null;
		
		try{
			getCountry = conn.prepareStatement("SELECT country FROM Countries WHERE countryID=?");
			getCountry.setObject(1, countryID);
			ResultSet rs = getCountry.executeQuery();
			if (rs.next()){
				o = rs.getObject("country");
				if (!(o instanceof String))
					return null;
				country = (String) o;
			}
			getCountry.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return country;
	}
	
	public static Integer getCountryID(Connection conn, String country){
		PreparedStatement getCountryID = null;
		Object o = null;
		Integer countryID = null;
		
		try{
			getCountryID = conn.prepareStatement("SELECT countryID FROM Countries WHERE country=?");
			getCountryID.setString(1, country);
			ResultSet rs = getCountryID.executeQuery();
			if (rs.next()){
				o = rs.getObject("countryID");
				if (!(o instanceof Integer))
					return null;
				countryID = (Integer) o;
			}
			getCountryID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return countryID;
	}
	
	public static List<Movie> getRecord(Connection conn, BigInteger directorID){
		PreparedStatement getRecordIDs = null;
		
		List<Movie> recordIDs = new LinkedList<Movie>();
		Movie movie = null;
		
		try{
			getRecordIDs = conn.prepareStatement("SELECT * FROM Movies WHERE directorID=?");
			getRecordIDs.setObject(1, directorID);
			ResultSet rs = getRecordIDs.executeQuery();
			while (rs.next()){
				movie = MovieTools.parseMovie(rs);
				if (movie!=null)
					recordIDs.add(movie);
			}
			getRecordIDs.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return recordIDs;
	}
	
	public static List<BigInteger> getRecordIDs(Connection conn, BigInteger directorID){
		PreparedStatement getRecordIDs = null;
		Object o = null;
		BigInteger movieID = null;
		List<BigInteger> recordIDs = new LinkedList<BigInteger>();
		
		try{
			getRecordIDs = conn.prepareStatement("SELECT movieID FROM Movies WHERE directorID=?");
			getRecordIDs.setObject(1, directorID);
			ResultSet rs = getRecordIDs.executeQuery();
			while (rs.next()){
				o = rs.getObject("movieID");
				if (!(o instanceof BigInteger))
					continue;
				movieID = (BigInteger) o;
				
				recordIDs.add(movieID);
			}
			getRecordIDs.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return recordIDs;
	}
	
	public static Director getDirector(Connection conn, BigInteger directorID){
		PreparedStatement getDirector = null;
		Object o = null;
		String directorName = null;
		String imdbName = null;
		
		try{
			getDirector = conn.prepareStatement("SELECT directorName,imdbName FROM Directors WHERE directorID=?");
			getDirector.setObject(1, directorID);
			ResultSet rs = getDirector.executeQuery();
			if (rs.next()){
				o = rs.getObject("directorName");
				if (!(o instanceof String))
					return null;
				directorName = (String) o;
				
				o = rs.getObject("imdbName");
				if (!(o instanceof String))
					return null;
				imdbName = (String) o;
			}
			getDirector.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		if (imdbName==null)
			return null;
		
		return new DirectorImp(directorID,directorName,imdbName);
	}
	
	public static BigInteger getDirectorID(Connection conn, String directorName){
		PreparedStatement getDirectorID = null;
		Object o = null;
		BigInteger directorID = null;
		
		try{
			getDirectorID = conn.prepareStatement("SELECT directorID FROM Directors WHERE directorName=?");
			getDirectorID.setString(1, directorName);
			ResultSet rs = getDirectorID.executeQuery();
			if (rs.next()){
				o = rs.getObject("directorID");
				if (!(o instanceof BigInteger))
					return null;
				directorID = (BigInteger) o;
			}
			getDirectorID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return directorID;
	}
	
	public static HashMap<String,BigInteger> getActors(Connection conn, String firstChar){
		PreparedStatement getActorID = null;
		Object o = null;
		BigInteger actorID = null;
		String actorName = null;
		
		HashMap<String,BigInteger> result = new HashMap<String,BigInteger>();
		
		try{
			getActorID = conn.prepareStatement("SELECT actorID, actorName FROM Actors WHERE actorName LIKE CONCAT(?,'%')");
			getActorID.setString(1, firstChar);
			ResultSet rs = getActorID.executeQuery();
			while (rs.next()){
				o = rs.getObject("actorID");
				if (!(o instanceof BigInteger))
					continue;
				actorID = (BigInteger) o;
				
				o = rs.getObject("actorName");
				if (!(o instanceof String))
					continue;
				actorName = (String) o;
				
				result.put(actorName, actorID);
			}
			getActorID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return result;
	}
	
	public static List<Role> getActorsList(Connection conn, BigInteger movieID){
		PreparedStatement getActorsList = null;
		Role role = null;
		List<Role> roles = new LinkedList<Role>();
		
		try{
			getActorsList = conn.prepareStatement("SELECT * FROM MovieActors WHERE movieID=?");
			getActorsList.setObject(1, movieID);
			ResultSet rs = getActorsList.executeQuery();
			while (rs.next()){
				role = MovieTools.parseRole(rs);		
				if (role!=null)
					roles.add(role);
			}
			getActorsList.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return roles;
	}
	
	public static List<Role> getRoles(Connection conn, BigInteger actorID){
		PreparedStatement getRoles = null;
		Role role = null;
		List<Role> roles = new LinkedList<Role>();
		
		try{
			getRoles = conn.prepareStatement("SELECT * FROM MovieActors WHERE actorID=?");
			getRoles.setObject(1, actorID);
			ResultSet rs = getRoles.executeQuery();
			while (rs.next()){
				role = MovieTools.parseRole(rs);		
				if (role!=null)
					roles.add(role);
			}
			getRoles.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return roles;
	}
	
	public static Role getRole(Connection conn, BigInteger movieID, BigInteger actorID){
		PreparedStatement getRole = null;
		Role role = null;
		
		try{
			getRole = conn.prepareStatement("SELECT * FROM MovieActors WHERE movieID=? AND actorID=?");
			getRole.setObject(1, movieID);
			getRole.setObject(2, actorID);
			ResultSet rs = getRole.executeQuery();
			if (rs.next()){
				role = MovieTools.parseRole(rs);
			}
			getRole.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return role;
	}
	
	public static Actor getActor(Connection conn, BigInteger actorID){
		PreparedStatement getActor = null;
		Actor actor = null;
		
		try{
			getActor = conn.prepareStatement("SELECT * FROM Actors WHERE actorID=?");
			getActor.setObject(1, actorID);
			ResultSet rs = getActor.executeQuery();
			if (rs.next()){
				actor = MovieTools.parseActor(rs);
			}
			getActor.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return actor;
	}
	
	public static BigInteger getActorID(Connection conn, String actorName){
		PreparedStatement getActorID = null;
		Object o = null;
		BigInteger actorID = null;
		
		try{
			getActorID = conn.prepareStatement("SELECT actorID FROM Actors WHERE actorName=?");
			getActorID.setString(1, actorName);
			ResultSet rs = getActorID.executeQuery();
			if (rs.next()){
				o = rs.getObject("actorID");
				if (!(o instanceof BigInteger))
					return null;
				actorID = (BigInteger) o;
			}
			getActorID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return actorID;
	}
	
	public static String getGenre(Connection conn, Integer genreID){
		PreparedStatement getGenre = null;
		Object o = null;
		String genre = null;
		
		try{
			getGenre = conn.prepareStatement("SELECT genre FROM Genres WHERE genreID=?");
			getGenre.setObject(1, genreID);
			ResultSet rs = getGenre.executeQuery();
			if (rs.next()){
				o = rs.getObject("genre");
				if (!(o instanceof String))
					return null;
				genre = (String) o;
			}
			getGenre.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return genre;
	}
	
	public static Integer getGenreID(Connection conn, String genre){
		PreparedStatement getGenreID = null;
		Object o = null;
		Integer genreID = null;
		
		try{
			getGenreID = conn.prepareStatement("SELECT genreID FROM Genres WHERE genre=?");
			getGenreID.setString(1, genre);
			ResultSet rs = getGenreID.executeQuery();
			if (rs.next()){
				o = rs.getObject("genreID");
				if (!(o instanceof Integer))
					return null;
				genreID = (Integer) o;
			}
			getGenreID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return genreID;
	}
	
	public static String getMPAARating(Connection conn, Integer mpaaRatingID){
		PreparedStatement getMPAARating = null;
		Object o = null;
		String mpaaRating = null;
		
		try{
			getMPAARating = conn.prepareStatement("SELECT mpaaRating FROM MPAARatings WHERE mpaaRatingID=?");
			getMPAARating.setObject(1, mpaaRatingID);
			ResultSet rs = getMPAARating.executeQuery();
			if (rs.next()){
				o = rs.getObject("mpaaRating");
				if (!(o instanceof String))
					return null;
				mpaaRating = (String) o;
			}
			getMPAARating.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return mpaaRating;
	}
	
	public static Integer getMPAARatingID(Connection conn, String mpaaRating){
		PreparedStatement getMPAARatingID = null;
		Object o = null;
		Integer mpaaRatingID = null;
		
		try{
			getMPAARatingID = conn.prepareStatement("SELECT mpaaRatingID FROM MPAARatings WHERE mpaaRating=?");
			getMPAARatingID.setString(1, mpaaRating);
			ResultSet rs = getMPAARatingID.executeQuery();
			if (rs.next()){
				o = rs.getObject("mpaaRatingID");
				if (!(o instanceof Integer))
					return null;
				mpaaRatingID = (Integer) o;
			}
			getMPAARatingID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return mpaaRatingID;
	}
	
	public static HashMap<String,BigInteger> getMovies(Connection conn, String firstChar){
		PreparedStatement getMovieID = null;
		Object o = null;
		BigInteger movieID = null;
		String movieName = null;
		
		HashMap<String,BigInteger> result = new HashMap<String,BigInteger>();
		
		try{
			getMovieID = conn.prepareStatement("SELECT movieID, movieName FROM Movies WHERE movieName LIKE CONCAT(?,'%')");
			getMovieID.setString(1, firstChar);
			ResultSet rs = getMovieID.executeQuery();
			while (rs.next()){
				o = rs.getObject("movieID");
				if (!(o instanceof BigInteger))
					continue;
				movieID = (BigInteger) o;
				
				o = rs.getObject("movieName");
				if (!(o instanceof String))
					continue;
				movieName = (String) o;
				
				result.put(movieName, movieID);
			}
			getMovieID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return result;
	}
	
	public static BigInteger getMovieID(Connection conn, String imdbName){
		PreparedStatement getMovieID = null;
		Object o = null;
		BigInteger movieID = null;
		
		try{
			getMovieID = conn.prepareStatement("SELECT movieID FROM Movies WHERE imdbName=?");
			getMovieID.setString(1, imdbName);
			ResultSet rs = getMovieID.executeQuery();
			if (rs.next()){
				o = rs.getObject("movieID");
				if (!(o instanceof BigInteger))
					return null;
				movieID = (BigInteger) o;
			}
			getMovieID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return movieID;
	}
	
	public static Movie getMovie(Connection conn, BigInteger movieID){
		PreparedStatement getMovieID = null;
		Movie movie = null;
		
		try{
			getMovieID = conn.prepareStatement("SELECT * FROM MoviesWithData WHERE movieID=?");
			getMovieID.setObject(1, movieID);
			ResultSet rs = getMovieID.executeQuery();
			if (rs.next()){
				movie = MovieTools.parseMovie(rs);
			}
			getMovieID.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return movie;
	}
	
	public static String getMovieDescription(Connection conn, BigInteger movieID){
		PreparedStatement getMovieDescription = null;
		Object o = null;
		String description = null;
		
		try{
			getMovieDescription = conn.prepareStatement("SELECT description FROM MovieDescriptions WHERE movieID=?");
			getMovieDescription.setObject(1, movieID);
			ResultSet rs = getMovieDescription.executeQuery();
			if (rs.next()){
				o = rs.getObject("description");
				if (!(o instanceof String))
					return null;
				description = (String) o;
			}
			getMovieDescription.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return description;
	}
	
	public static User getUser(Connection conn, BigInteger userID){
		PreparedStatement getUser = null;
		User user = null;
		
		try{
			getUser = conn.prepareStatement("SELECT * FROM Users WHERE userID=?");
			getUser.setObject(1, userID);
			ResultSet rs = getUser.executeQuery();
			if (rs.next()){
				user = MovieTools.parseUser(rs);
			}
			getUser.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return user;
	}
	
	public static User getUserByName(Connection conn, String username){
		PreparedStatement getUserByName = null;
		User user = null;
		
		try{
			getUserByName = conn.prepareStatement("SELECT * FROM Users WHERE username=?");
			getUserByName.setString(1, username);
			ResultSet rs = getUserByName.executeQuery();
			if (rs.next()){
				user = MovieTools.parseUser(rs);
			}
			getUserByName.close();
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return user;
	}
	
	public static List<Movie> getWishList(Connection conn, BigInteger userID){
		return MovieTools.getMovieListing(conn,userID,"WishList");
	}
	
	public static List<Movie> getMustSee(Connection conn, BigInteger userID){
		return MovieTools.getMovieListing(conn,userID,"MustSee");
	}
	
	public static List<Movie> getAlreadySeen(Connection conn, BigInteger userID){
		return MovieTools.getMovieListing(conn,userID,"AlreadySeen");
	}
	
	public static List<Movie> getMovieListing(Connection conn, BigInteger userID, String table){
		PreparedStatement deleteMovieListing = null;
		List<Movie> movies = new LinkedList<Movie>();
		Movie movie = null;
		
		try{
			String sql = "SELECT MoviesWithData.movieID AS movieID, imdbName, movieName, year, countryID, languageID, directorID, length, isColor, mpaaRatingID, userRating, country, language, mpaaRating " +
							"FROM " + table + " INNER JOIN MoviesWithData ON MoviesWithData.movieID=" + table + ".movieID WHERE userID=?";
			
			deleteMovieListing = conn.prepareStatement(sql);
			
			/*deleteMovieListing = conn.prepareStatement("SELECT Movies.movieID AS movieID, imdbName, movieName, year, Movies.countryID AS countryID, Movies.languageID AS languageID, " +
														"directorID, length, isColor, Movies.mpaaRatingID as mpaaRatingID, userRating, country, language, mpaaRating " +
														"FROM " + table + " INNER JOIN Movies ON Movies.movieID=" + table + ".movieID " +
														"LEFT OUTER JOIN Countries ON Movie.countryID=Contries.countryID " +
														"LEFT OUTER JOIN Languages ON Movies.languageID=Languages.languageID " +
														"LEFT OUTER JOIN MPAARatings ON Movies.mpaaRatingID=MPAARatings.mpaaRatingID " +
														"WHERE userID=?");*/
			deleteMovieListing.setObject(1, userID);
			ResultSet rs = deleteMovieListing.executeQuery();
			while (rs.next()){
				movie = MovieTools.parseMovie(rs);
				
				if (movie!=null)
					movies.add(movie);
			}
		}
	    catch (Exception e){
	    	return null;
	    }
		
		return movies;
	}
	
	
	// Database updaters/inserters
	
	public static boolean updateDescription(Connection conn, BigInteger movieID, String description){
		PreparedStatement updateDescription = null;
		
		try{
			updateDescription = conn.prepareStatement("INSERT INTO MovieDescriptions (movieID,description) VALUES (?,?) " +
														"ON DUPLICATE KEY UPDATE description=?");
			
			//Insert
			updateDescription.setObject(1, movieID);
			updateDescription.setString(2, description);
			
			//Update
			updateDescription.setString(3, description);
			
			updateDescription.executeUpdate();
			conn.commit();
			updateDescription.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateLanguage(Connection conn, Language language){
		PreparedStatement updateLanguage = null;
		
		try{
			if (language.getID() != null){
				updateLanguage = conn.prepareStatement("INSERT INTO Languages (languageID,language) VALUES (?,?) " +
														"ON DUPLICATE KEY UPDATE language=?");
				
				//Insert
				updateLanguage.setObject(1, language.getID());
				updateLanguage.setObject(2, language.getLanguage());
				
				//Update
				updateLanguage.setObject(3, language.getLanguage());
			}
			
			else{
				updateLanguage = conn.prepareStatement("INSERT INTO Languages (language) VALUES (?)");
				updateLanguage.setObject(1, language.getLanguage());
			}
			
			updateLanguage.executeUpdate();
			conn.commit();
			updateLanguage.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateCountry(Connection conn, Country country){
		PreparedStatement updateCountry = null;
		
		try{
			if (country.getID() != null){
				updateCountry = conn.prepareStatement("INSERT INTO Countries (countryID,country) VALUES (?,?) " +
														"ON DUPLICATE KEY UPDATE country=?");
				
				//Insert
				updateCountry.setObject(1, country.getID());
				updateCountry.setObject(2, country.getCountry());
				
				//Update
				updateCountry.setObject(3, country.getCountry());
			}
			
			else{
				updateCountry = conn.prepareStatement("INSERT INTO Countries (country) VALUES (?)");
				updateCountry.setObject(1, country.getCountry());
			}
			
			updateCountry.executeUpdate();
			conn.commit();
			updateCountry.close();
			
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateMPAARating(Connection conn, MPAARating mpaaRating){
		PreparedStatement updateMPAARating = null;
		
		try{
			if (mpaaRating.getID() != null){
				updateMPAARating = conn.prepareStatement("INSERT INTO MPAARatings (mpaaRatingID,mpaaRating) VALUES(?,?) " +
															"ON DUPLICATE KEY UPDATE mpaaRating=?");
				
				//Insert
				updateMPAARating.setObject(1, mpaaRating.getID());
				updateMPAARating.setObject(2, mpaaRating.getMPAARating());
				
				//Update
				updateMPAARating.setObject(3, mpaaRating.getMPAARating());
			}
			
			else{
				updateMPAARating = conn.prepareStatement("INSERT INTO MPAARatings (mpaaRating) VALUES(?)");
				updateMPAARating.setObject(1, mpaaRating.getMPAARating());
			}
			
			updateMPAARating.executeUpdate();
			conn.commit();
			updateMPAARating.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateGenre(Connection conn, Genre genre){
		PreparedStatement updateGenre = null;
		
		try{
			if (genre.getID() != null){
				updateGenre = conn.prepareStatement("INSERT INTO Genres (genreID,genre) VALUES (?,?) " +
													"ON DUPLICATE KEY UPDATE genre=?");
				
				//Insert
				updateGenre.setObject(1, genre.getID());
				updateGenre.setObject(2, genre.getGenre());
				
				//Update
				updateGenre.setObject(3, genre.getGenre());
			}
			
			else{
				updateGenre = conn.prepareStatement("INSERT INTO Genres (genre) VALUES (?)");
				updateGenre.setObject(1, genre.getGenre());
			}
			
			updateGenre.executeUpdate();
			conn.commit();
			updateGenre.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateRole(Connection conn, Role role){
		PreparedStatement updateRole = null;
		
		try{
			updateRole = conn.prepareStatement("INSERT INTO Roles (movieID,actorID,role) VALUES (?,?,?) " +
												"ON DUPLICATE KEY UPDATE actorID=?,role=?");
			
			//Insert
			updateRole.setObject(1, role.getMovieID());
			updateRole.setObject(2, role.getActorID());
			updateRole.setString(3, role.getRole());
			
			//Update
			updateRole.setObject(4, role.getActorID());
			updateRole.setString(5, role.getRole());
			
			updateRole.executeUpdate();
			conn.commit();
			updateRole.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateDirector(Connection conn, Director director){
		PreparedStatement updateDirector = null;
		
		try{
			if (director.getID() != null){
				updateDirector = conn.prepareStatement("INSERT INTO Directors (directorID,directorName,imdbName) VALUES (?,?,?) " +
														"ON DUPLICATE KEY UPDATE directorName=?,imdbName=?");
				
				//Insert
				updateDirector.setObject(1, director.getID());
				updateDirector.setString(2, director.getName());
				updateDirector.setString(3, director.getImdbName());
				
				// Update
				updateDirector.setString(4, director.getName());
				updateDirector.setString(5, director.getImdbName());
			}
			
			else{
				updateDirector = conn.prepareStatement("INSERT INTO Directors (directorName,imdbName) VALUES (?,?)");
				updateDirector.setString(1, director.getName());
				updateDirector.setString(2, director.getImdbName());
			}
			
			updateDirector.executeUpdate();
			conn.commit();
			updateDirector.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateActor(Connection conn, Actor actor){
		PreparedStatement updateActor = null;
		
		try{
			if (actor.getID() != null){
				updateActor = conn.prepareStatement("INSERT INTO Actors (actorID,directorName,imdbName,gender) VALUES (?,?,?,?) " +
													"ON DUPLICATE KEY UPDATE directorName=?,imdbName=?,gender=?");
				
				// Insert
				updateActor.setObject(1, actor.getID());
				updateActor.setString(2, actor.getName());
				updateActor.setString(3, actor.getImdbName());
				updateActor.setString(4, actor.getGenderString());
				
				//Update
				updateActor.setString(5, actor.getName());
				updateActor.setString(6, actor.getImdbName());
				updateActor.setString(7, actor.getGenderString());
			}
			
			else{
				updateActor = conn.prepareStatement("INSERT INTO Actors (directorName,imdbName,gender) VALUES (?,?,?)");
				
				updateActor.setString(1, actor.getName());
				updateActor.setString(2, actor.getImdbName());
				updateActor.setString(3, actor.getGenderString());
			}
			
			updateActor.executeUpdate();
			conn.commit();
			updateActor.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateUser(Connection conn, User user){
		PreparedStatement updateUser = null;
		
		try{
			if (user.getID() != null){
				updateUser = conn.prepareStatement("INSERT INTO Users (userID,username,password,admin) VALUES (?,?,?,?) " +
													"ON DUPLICATE KEY UPDATE userName=?,password=?,admin=?");
				
				//Insert
				updateUser.setObject(1, user.getID());
				updateUser.setString(2, user.getUsername());
				updateUser.setString(3, user.getPassword());
				updateUser.setBoolean(4, user.isAdmin());
				
				//Update
				updateUser.setString(5, user.getUsername());
				updateUser.setString(6, user.getPassword());
				updateUser.setBoolean(7, user.isAdmin());
			}
			
			else{
				updateUser = conn.prepareStatement("INSERT INTO Users (username,password,admin) VALUES (?,?,?)");
				updateUser.setString(1, user.getUsername());
				updateUser.setString(2, user.getPassword());
				updateUser.setBoolean(3, user.isAdmin());
			}
			
			updateUser.executeUpdate();
			conn.commit();
			updateUser.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean updateMovie(Connection conn, Movie movie){
		PreparedStatement updateMovie = null;
		
		try{
			if (movie.getID() != null){
				String sql = "INSERT INTO Movies (movieID,movieName,imdbName,year,countryID,languageID,directorID,length,isColor,mpaaRatingID,userRating) VALUES (?,?,?,?,?,?,?,?,?,?,?) " +
								"ON DUPLICATE KEY UPDATE movieName=?,imdbName=?,year=?,countryID=?,languageID=?,directorID=?,length=?,isColor=?,mpaaRatingID=?,userRating=?";
				
				updateMovie = conn.prepareStatement(sql);
				
				//Insert
				updateMovie.setObject(1, movie.getID());
				updateMovie.setString(2, movie.getName());
				updateMovie.setString(3, movie.getImdbName());
				updateMovie.setObject(4, movie.getYear());
				updateMovie.setObject(5, movie.getCountryID());
				updateMovie.setObject(6, movie.getLanguageID());
				updateMovie.setObject(7, movie.getDirectorID());
				updateMovie.setObject(8, movie.getLength());
				updateMovie.setBoolean(9, movie.isColor());
				updateMovie.setObject(10, movie.getMPAARatingID());
				updateMovie.setObject(11, movie.getOurRating());
				
				//Update
				updateMovie.setString(12, movie.getName());
				updateMovie.setString(13, movie.getImdbName());
				updateMovie.setObject(14, movie.getYear());
				updateMovie.setObject(15, movie.getCountryID());
				updateMovie.setObject(16, movie.getLanguageID());
				updateMovie.setObject(17, movie.getDirectorID());
				updateMovie.setObject(18, movie.getLength());
				updateMovie.setBoolean(19, movie.isColor());
				updateMovie.setObject(20, movie.getMPAARatingID());
				updateMovie.setObject(21, movie.getOurRating());
			}
			
			else{
				String sql = "INSERT INTO Movies (movieName,imdbName,year,countryID,languageID,directorID,length,isColor,mpaaRatingID,userRating) VALUES (?,?,?,?,?,?,?,?,?,?)";
				updateMovie = conn.prepareStatement(sql);
				updateMovie.setString(1, movie.getName());
				updateMovie.setString(2, movie.getImdbName());
				updateMovie.setObject(3, movie.getYear());
				updateMovie.setObject(4, movie.getCountryID());
				updateMovie.setObject(5, movie.getLanguageID());
				updateMovie.setObject(6, movie.getDirectorID());
				updateMovie.setObject(7, movie.getLength());
				updateMovie.setBoolean(8, movie.isColor());
				updateMovie.setObject(9, movie.getMPAARatingID());
				updateMovie.setObject(10, movie.getOurRating());
			}
			
			updateMovie.executeUpdate();
			conn.commit();
			updateMovie.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	// Database adders/misc.
	
	public static boolean setGenre(Connection conn, BigInteger movieID, List<Genre> genres){
		PreparedStatement stmt = null;
		try{
			
			stmt = conn.prepareStatement("DELETE FROM MovieGenres WHERE movieID=?");
			stmt.setObject(1, movieID);
			stmt.execute();
			
			stmt = conn.prepareStatement("REPLACE INTO MovieGenres (movieID,genreID) VALUES(?,?)");
			stmt.setObject(1, movieID);
			
			for (Genre genre : genres){
				if (genre != null){
					stmt.setObject(2, genre.getID());
					stmt.addBatch();
				}
			}
			stmt.executeBatch();
			
			conn.commit();
			stmt.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean addMovieGenre(Connection conn, BigInteger movieID, Integer genreID){
		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement("REPLACE INTO MovieGenres (movieID,genreID) VALUES(?,?)");
			stmt.setObject(1, movieID);
			stmt.setObject(2, genreID);
			stmt.executeUpdate();
			conn.commit();
			stmt.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean addWishList(Connection conn, BigInteger userID, BigInteger movieID){
		return MovieTools.addMovieListing(conn,userID,movieID,"WishList");
	}
	
	public static boolean addMustSee(Connection conn, BigInteger userID, BigInteger movieID){
		return MovieTools.addMovieListing(conn,userID,movieID,"MustSee");
	}
	
	public static boolean addAlreadySeen(Connection conn, BigInteger userID, BigInteger movieID){
		return MovieTools.addMovieListing(conn,userID,movieID,"AlreadySeen");
	}
	
	public static boolean addMovieListing(Connection conn, BigInteger userID, BigInteger movieID, String table){
		PreparedStatement deleteMovieListing = null;
		try{
			deleteMovieListing = conn.prepareStatement("REPLACE INTO " + table + " (userID,movieID) VALUES(?,?)");
			deleteMovieListing.setObject(1, userID);
			deleteMovieListing.setObject(2, movieID);
			deleteMovieListing.executeUpdate();
			conn.commit();
			deleteMovieListing.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	// Database deleters
	
	public static boolean deleteObject(Connection conn, DBObject o){
		PreparedStatement deleteObject = null;
		
		try{
			String table = null;
			String property = null;
			String sql = null;
			
			boolean isRole = false;
			
			if (o instanceof Role){ //Special case where there is no single primary key.
				sql = "DELETE FROM MovieActors WHERE movieID=? AND actorID=?";
				isRole = true;
			}
			else{
				if (o instanceof Actor){
					table = "Actors";
					property = "actorID";
					
					//Maintain Foreign Key of MovieActors
					deleteObject = conn.prepareStatement("DELETE FROM MovieActors WHERE actorID=?");
					deleteObject.setObject(1, o.getID());
					deleteObject.execute();
					conn.commit();
					deleteObject.close();
				}
				else if (o instanceof Country){
					table = "Countries";
					property = "countryID";
				}
				else if (o instanceof Director){
					table = "Directors";
					property = "directorID";
				}
				else if (o instanceof Genre){
					table = "Genres";
					property = "genreID";
				}
				else if (o instanceof Language){
					table = "Languages";
					property = "languageID";
				}
				else if (o instanceof Movie){
					table = "Movies";
					property = "movieID";
					
					//Maintain Foreign Key of MovieActors
					deleteObject = conn.prepareStatement("DELETE FROM MovieActors WHERE movieID=?");
					deleteObject.setObject(1, o.getID());
					deleteObject.execute();
					conn.commit();
					deleteObject.close();
				}
				else if (o instanceof MPAARating){
					table = "MPAARatings";
					property = "mpaaRatingID";
				}
				else if (o instanceof User){
					table = "Users";
					property = "userID";
				}
				
				sql = "DELETE FROM " + table + " WHERE " + property + "=?";
			}
			
			deleteObject = conn.prepareStatement(sql);
			
			if (!isRole)
				deleteObject.setObject(1, o.getID());
			else{
				Role role = (Role) o;
				deleteObject.setObject(1, role.getMovieID());
				deleteObject.setObject(2, role.getActorID());
			}
			
			deleteObject.execute();
			conn.commit();
			deleteObject.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean deleteMovieGenre(Connection conn, BigInteger movieID, Integer genreID){
		PreparedStatement deleteMovieGenre = null;
		try{
			deleteMovieGenre = conn.prepareStatement("DELETE FROM MovieGenres WHERE movieID=? AND genreID=?");
			deleteMovieGenre.setObject(1, movieID);
			deleteMovieGenre.setObject(2, genreID);
			deleteMovieGenre.execute();
			conn.commit();
			deleteMovieGenre.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
	
	public static boolean deleteWishList(Connection conn, BigInteger userID, BigInteger movieID){
		return MovieTools.deleteMovieListing(conn,userID,movieID,"WishList");
	}
	
	public static boolean deleteMustSee(Connection conn, BigInteger userID, BigInteger movieID){
		return MovieTools.deleteMovieListing(conn,userID,movieID,"MustSee");
	}
	
	public static boolean deleteAlreadySeen(Connection conn, BigInteger userID, BigInteger movieID){
		return MovieTools.deleteMovieListing(conn,userID,movieID,"AlreadySeen");
	}
	
	public static boolean deleteMovieListing(Connection conn, BigInteger userID, BigInteger movieID, String table){
		PreparedStatement deleteMovieListing = null;
		try{
			deleteMovieListing = conn.prepareStatement("DELETE FROM " + table + " WHERE userID=? AND movieID=?");
			deleteMovieListing.setObject(1, userID);
			deleteMovieListing.setObject(2, movieID);
			deleteMovieListing.execute();
			conn.commit();
			deleteMovieListing.close();
		}
	    catch (Exception e){
	    	return false;
	    }
		
		return true;
	}
}
